﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;

using HsTec.Mobile.Green.Util;

namespace HsTec.Mobile.Green.DAL
{
    /// <summary>
    /// 数据访问类TotalProject。
    /// </summary>
    public class TotalProjectDAL
    {
        public TotalProjectDAL()
        { }
        #region  成员方法
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string sExistsWhere)
        {
            return SQLHelper.Exists("pm_TotalProject", sExistsWhere);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(HsTec.Mobile.Green.Model.TotalProject model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into pm_TotalProject(");
            strSql.Append("tasktype,stage,taskno,projectno,commpany,name,leaders,projecttype,progresstype,person1,person2,person3,planner,tpdate,business,comments1,comments2,reviewedchange,meetingchange,remark,cooperationunit,cooperationperson)");
            strSql.Append(" values (");
            strSql.Append("@tasktype,@stage,@taskno,@projectno,@commpany,@name,@leaders,@projecttype,@progresstype,@person1,@person2,@person3,@planner,@tpdate,@business,@comments1,@comments2,@reviewedchange,@meetingchange,@remark,@cooperationunit,@cooperationperson)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@tasktype", SqlDbType.NVarChar,50),
					new SqlParameter("@stage", SqlDbType.NVarChar,50),
					new SqlParameter("@taskno", SqlDbType.NVarChar,50),
					new SqlParameter("@projectno", SqlDbType.NVarChar,50),
					new SqlParameter("@commpany", SqlDbType.NVarChar,200),
					new SqlParameter("@name", SqlDbType.NVarChar,200),
					new SqlParameter("@leaders", SqlDbType.NVarChar,50),
					new SqlParameter("@projecttype", SqlDbType.NVarChar,50),
					new SqlParameter("@progresstype", SqlDbType.NVarChar,50),
					new SqlParameter("@person1", SqlDbType.NVarChar,50),
					new SqlParameter("@person2", SqlDbType.NVarChar,50),
					new SqlParameter("@person3", SqlDbType.NVarChar,50),
					new SqlParameter("@planner", SqlDbType.NVarChar,50),
					new SqlParameter("@tpdate", SqlDbType.DateTime),
					new SqlParameter("@business", SqlDbType.NVarChar,50),
					new SqlParameter("@comments1", SqlDbType.NVarChar,200),
					new SqlParameter("@comments2", SqlDbType.NVarChar,200),
					new SqlParameter("@reviewedchange", SqlDbType.NVarChar,200),
					new SqlParameter("@meetingchange", SqlDbType.NVarChar,200),
					new SqlParameter("@remark", SqlDbType.NVarChar,200),
					new SqlParameter("@cooperationunit", SqlDbType.NVarChar,200),
					new SqlParameter("@cooperationperson", SqlDbType.NVarChar,200)};
            parameters[0].Value = model.tasktype;
            parameters[1].Value = model.stage;
            parameters[2].Value = model.taskno;
            parameters[3].Value = model.projectno;
            parameters[4].Value = model.commpany;
            parameters[5].Value = model.name;
            parameters[6].Value = model.leaders;
            parameters[7].Value = model.projecttype;
            parameters[8].Value = model.progresstype;
            parameters[9].Value = model.person1;
            parameters[10].Value = model.person2;
            parameters[11].Value = model.person3;
            parameters[12].Value = model.planner;
            parameters[13].Value = model.tpdate;
            parameters[14].Value = model.business;
            parameters[15].Value = model.comments1;
            parameters[16].Value = model.comments2;
            parameters[17].Value = model.reviewedchange;
            parameters[18].Value = model.meetingchange;
            parameters[19].Value = model.remark;
            parameters[20].Value = model.cooperationunit;
            parameters[21].Value = model.cooperationperson;

            return SQLHelper.ExecuteNonQuery(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public int Update(HsTec.Mobile.Green.Model.TotalProject model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update pm_TotalProject set ");
            strSql.Append("tasktype=@tasktype,");
            strSql.Append("stage=@stage,");
            strSql.Append("taskno=@taskno,");
            strSql.Append("projectno=@projectno,");
            strSql.Append("commpany=@commpany,");
            strSql.Append("name=@name,");
            strSql.Append("leaders=@leaders,");
            strSql.Append("projecttype=@projecttype,");
            strSql.Append("progresstype=@progresstype,");
            strSql.Append("person1=@person1,");
            strSql.Append("person2=@person2,");
            strSql.Append("person3=@person3,");
            strSql.Append("planner=@planner,");
            strSql.Append("tpdate=@tpdate,");
            strSql.Append("business=@business,");
            strSql.Append("comments1=@comments1,");
            strSql.Append("comments2=@comments2,");
            strSql.Append("reviewedchange=@reviewedchange,");
            strSql.Append("meetingchange=@meetingchange,");
            strSql.Append("remark=@remark,");
            strSql.Append("cooperationunit=@cooperationunit,");
            strSql.Append("cooperationperson=@cooperationperson");
            strSql.Append(" where tp_id=@tp_id ");
            SqlParameter[] parameters = {
					new SqlParameter("@tp_id", SqlDbType.Int,4),
					new SqlParameter("@tasktype", SqlDbType.NVarChar,50),
					new SqlParameter("@stage", SqlDbType.NVarChar,50),
					new SqlParameter("@taskno", SqlDbType.NVarChar,50),
					new SqlParameter("@projectno", SqlDbType.NVarChar,50),
					new SqlParameter("@commpany", SqlDbType.NVarChar,200),
					new SqlParameter("@name", SqlDbType.NVarChar,200),
					new SqlParameter("@leaders", SqlDbType.NVarChar,50),
					new SqlParameter("@projecttype", SqlDbType.NVarChar,50),
					new SqlParameter("@progresstype", SqlDbType.NVarChar,50),
					new SqlParameter("@person1", SqlDbType.NVarChar,50),
					new SqlParameter("@person2", SqlDbType.NVarChar,50),
					new SqlParameter("@person3", SqlDbType.NVarChar,50),
					new SqlParameter("@planner", SqlDbType.NVarChar,50),
					new SqlParameter("@tpdate", SqlDbType.DateTime),
					new SqlParameter("@business", SqlDbType.NVarChar,50),
					new SqlParameter("@comments1", SqlDbType.NVarChar,200),
					new SqlParameter("@comments2", SqlDbType.NVarChar,200),
					new SqlParameter("@reviewedchange", SqlDbType.NVarChar,200),
					new SqlParameter("@meetingchange", SqlDbType.NVarChar,200),
					new SqlParameter("@remark", SqlDbType.NVarChar,200),
					new SqlParameter("@cooperationunit", SqlDbType.NVarChar,200),
					new SqlParameter("@cooperationperson", SqlDbType.NVarChar,200)};
            parameters[0].Value = model.tp_id;
            parameters[1].Value = model.tasktype;
            parameters[2].Value = model.stage;
            parameters[3].Value = model.taskno;
            parameters[4].Value = model.projectno;
            parameters[5].Value = model.commpany;
            parameters[6].Value = model.name;
            parameters[7].Value = model.leaders;
            parameters[8].Value = model.projecttype;
            parameters[9].Value = model.progresstype;
            parameters[10].Value = model.person1;
            parameters[11].Value = model.person2;
            parameters[12].Value = model.person3;
            parameters[13].Value = model.planner;
            parameters[14].Value = model.tpdate;
            parameters[15].Value = model.business;
            parameters[16].Value = model.comments1;
            parameters[17].Value = model.comments2;
            parameters[18].Value = model.reviewedchange;
            parameters[19].Value = model.meetingchange;
            parameters[20].Value = model.remark;
            parameters[21].Value = model.cooperationunit;
            parameters[22].Value = model.cooperationperson;

            return SQLHelper.ExecuteNonQuery(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public int Delete(string sIDs)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from pm_TotalProject ");
            strSql.Append(" where tp_id in ( " + sIDs + " )");
            return SQLHelper.ExecuteNonQuery(strSql.ToString());
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public HsTec.Mobile.Green.Model.TotalProject GetModel(int tp_id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 tp_id,tasktype,stage,taskno,projectno,commpany,name,leaders,projecttype,progresstype,person1,person2,person3,planner,tpdate,business,comments1,comments2,reviewedchange,meetingchange,remark,cooperationunit,cooperationperson from pm_TotalProject ");
            strSql.Append(" where tp_id=@tp_id ");
            SqlParameter[] parameters = {
					new SqlParameter("@tp_id", SqlDbType.Int,4)};
            parameters[0].Value = tp_id;


            HsTec.Mobile.Green.Model.TotalProject model = new HsTec.Mobile.Green.Model.TotalProject();
            DataSet ds = SQLHelper.ExecuteDataset(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["tp_id"].ToString() != "")
                {
                    model.tp_id = int.Parse(ds.Tables[0].Rows[0]["tp_id"].ToString());
                }
                model.tasktype = ds.Tables[0].Rows[0]["tasktype"].ToString();
                model.stage = ds.Tables[0].Rows[0]["stage"].ToString();
                model.taskno = ds.Tables[0].Rows[0]["taskno"].ToString();
                model.projectno = ds.Tables[0].Rows[0]["projectno"].ToString();
                model.commpany = ds.Tables[0].Rows[0]["commpany"].ToString();
                model.name = ds.Tables[0].Rows[0]["name"].ToString();
                model.leaders = ds.Tables[0].Rows[0]["leaders"].ToString();
                model.projecttype = ds.Tables[0].Rows[0]["projecttype"].ToString();
                model.progresstype = ds.Tables[0].Rows[0]["progresstype"].ToString();
                model.person1 = ds.Tables[0].Rows[0]["person1"].ToString();
                model.person2 = ds.Tables[0].Rows[0]["person2"].ToString();
                model.person3 = ds.Tables[0].Rows[0]["person3"].ToString();
                model.planner = ds.Tables[0].Rows[0]["planner"].ToString();
                if (ds.Tables[0].Rows[0]["tpdate"].ToString() != "")
                {
                    model.tpdate = DateTime.Parse(ds.Tables[0].Rows[0]["tpdate"].ToString());
                }
                model.business = ds.Tables[0].Rows[0]["business"].ToString();
                model.comments1 = ds.Tables[0].Rows[0]["comments1"].ToString();
                model.comments2 = ds.Tables[0].Rows[0]["comments2"].ToString();
                model.reviewedchange = ds.Tables[0].Rows[0]["reviewedchange"].ToString();
                model.meetingchange = ds.Tables[0].Rows[0]["meetingchange"].ToString();
                model.remark = ds.Tables[0].Rows[0]["remark"].ToString();
                model.cooperationunit = ds.Tables[0].Rows[0]["cooperationunit"].ToString();
                model.cooperationperson = ds.Tables[0].Rows[0]["cooperationperson"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }
        public HsTec.Mobile.Green.Model.TotalProjectEntity GetModelEntity(int tp_id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 tp_id,tasktype,stage,taskno,projectno,commpany,name,leaders,projecttype,progresstype,person1,person2,person3,planner,tpdate,business,comments1,comments2,reviewedchange,meetingchange,remark,cooperationunit,cooperationperson,personname1,personname2,personname3,plannername,businessname,leadersname from v_pm_TotalProject ");
            strSql.Append(" where tp_id=@tp_id ");
            SqlParameter[] parameters = {
					new SqlParameter("@tp_id", SqlDbType.Int,4)};
            parameters[0].Value = tp_id;


            HsTec.Mobile.Green.Model.TotalProjectEntity model = new HsTec.Mobile.Green.Model.TotalProjectEntity();
            DataSet ds = SQLHelper.ExecuteDataset(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["tp_id"].ToString() != "")
                {
                    model.tp_id = int.Parse(ds.Tables[0].Rows[0]["tp_id"].ToString());
                }
                model.tasktype = ds.Tables[0].Rows[0]["tasktype"].ToString();
                model.stage = ds.Tables[0].Rows[0]["stage"].ToString();
                model.taskno = ds.Tables[0].Rows[0]["taskno"].ToString();
                model.projectno = ds.Tables[0].Rows[0]["projectno"].ToString();
                model.commpany = ds.Tables[0].Rows[0]["commpany"].ToString();
                model.name = ds.Tables[0].Rows[0]["name"].ToString();
                model.leaders = ds.Tables[0].Rows[0]["leaders"].ToString();
                model.projecttype = ds.Tables[0].Rows[0]["projecttype"].ToString();
                model.progresstype = ds.Tables[0].Rows[0]["progresstype"].ToString();
                model.person1 = ds.Tables[0].Rows[0]["person1"].ToString();
                model.person2 = ds.Tables[0].Rows[0]["person2"].ToString();
                model.person3 = ds.Tables[0].Rows[0]["person3"].ToString();
                model.planner = ds.Tables[0].Rows[0]["planner"].ToString();
                if (ds.Tables[0].Rows[0]["tpdate"].ToString() != "")
                {
                    model.tpdate = DateTime.Parse(ds.Tables[0].Rows[0]["tpdate"].ToString());
                }
                model.business = ds.Tables[0].Rows[0]["business"].ToString();
                model.comments1 = ds.Tables[0].Rows[0]["comments1"].ToString();
                model.comments2 = ds.Tables[0].Rows[0]["comments2"].ToString();
                model.reviewedchange = ds.Tables[0].Rows[0]["reviewedchange"].ToString();
                model.meetingchange = ds.Tables[0].Rows[0]["meetingchange"].ToString();
                model.remark = ds.Tables[0].Rows[0]["remark"].ToString();
                model.cooperationunit = ds.Tables[0].Rows[0]["cooperationunit"].ToString();
                model.cooperationperson = ds.Tables[0].Rows[0]["cooperationperson"].ToString();

                model.personname1 = ds.Tables[0].Rows[0]["personname1"].ToString();
                model.personname2 = ds.Tables[0].Rows[0]["personname2"].ToString();
                model.personname3 = ds.Tables[0].Rows[0]["personname3"].ToString();
                model.plannername = ds.Tables[0].Rows[0]["plannername"].ToString();
                model.businessname = ds.Tables[0].Rows[0]["businessname"].ToString();
                model.leadersname = ds.Tables[0].Rows[0]["leadersname"].ToString();

                return model;
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataView GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select tp_id,tasktype,stage,taskno,projectno,commpany,name,leaders,projecttype,progresstype,person1,person2,person3,planner,tpdate,business,comments1,comments2,reviewedchange,meetingchange,remark,cooperationunit,cooperationperson ");
            strSql.Append(" FROM pm_TotalProject ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return SQLHelper.ExecuteDataView(strSql.ToString());
        }

        public DataView GetListEntity(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select tp_id,tasktype,stage,taskno,projectno,commpany,name,leaders,projecttype,progresstype,person1,person2,person3,planner,tpdate,business,comments1,comments2,reviewedchange,meetingchange,remark,cooperationunit,cooperationperson,_personname1,_personname2,_personname3,_plannername,_businessname,_leadersname ");
            strSql.Append(" FROM v_pm_TotalProject ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return SQLHelper.ExecuteDataView(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataView GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" tp_id,tasktype,stage,taskno,projectno,commpany,name,leaders,projecttype,progresstype,person1,person2,person3,planner,tpdate,business,comments1,comments2,reviewedchange,meetingchange,remark,cooperationunit,cooperationperson ");
            strSql.Append(" FROM pm_TotalProject ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return SQLHelper.ExecuteDataView(strSql.ToString());
        }

        #endregion  成员方法
    }
}

